package com.neuedu.oasystem.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.List;
import java.util.ArrayList;
import java.util.Date;

import com.neuedu.oasystem.dao.IEmployeeDao;

import com.neuedu.oasystem.entity.Employee;

import com.neuedu.oasystem.util.DBHelper;

/**
 * 
 * @author admit 员工方法实现类
 */
public class EmployeeDaoImpl extends BaseDao implements IEmployeeDao {
	/**
	 * 新增
	 */
	@Override
	public boolean add(Employee param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "insert into employee (emp_Id,dept_Id,role_id,emp_Name,"
					+ "password,sal,id_Number,address,phone_Number,sex,email"
					+ ",education,hiredate) values(EMP_ID_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, param.getDeptId());
			stmt.setInt(2, param.getRoleId());
			stmt.setString(3, param.getEmpName());
			stmt.setString(4, param.getPassword());
			stmt.setDouble(5, param.getSal());
			stmt.setString(6, param.getIdNumber());
			stmt.setString(7, param.getAdderss());
			stmt.setString(8, param.getPhoneNumber());
			stmt.setString(9, param.getSex());
			stmt.setString(10, param.getEmail());
			stmt.setString(11, param.getEducation());
			stmt.setTimestamp(12, new java.sql.Timestamp(param.getHiredate().getTime()));

			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	/**
	 * 修改
	 */
	@Override
	public boolean edit(Employee param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update employee set dept_Id=?,role_id =?,emp_Name=?,password=?,sal=?,id_Number=?,address=?,phone_Number=?,sex=?,email=?,education=?,hiredate=? where emp_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, param.getDeptId());
			stmt.setInt(2, param.getRoleId());
			stmt.setString(3, param.getEmpName());
			stmt.setString(4, param.getPassword());
			stmt.setDouble(5, param.getSal());
			stmt.setString(6, param.getPhoneNumber());
			stmt.setString(7, param.getAdderss());
			stmt.setString(8, param.getPhoneNumber());
			stmt.setString(9, param.getSex());
			stmt.setString(10, param.getEmail());
			stmt.setString(11, param.getEducation());
			stmt.setTimestamp(12, new java.sql.Timestamp(param.getHiredate().getTime()));
			stmt.setInt(13, param.getEmpId());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	/**
	 * 
	 * 删除
	 */
	@Override
	public boolean deleteById(int id) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "delete from employee where emp_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	/**
	 * 根据主键查询
	 */
	@Override
	public Employee findById(int empId) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		Employee employee = null;
		ResultSet rs = null;

		try {

			String sql = "select emp_Id,dept_Id,role_id,emp_Name,password,sal,id_Number,address,"
					+ "phone_Number,sex,email,education,hiredate from employee where emp_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, empId);
			rs = stmt.executeQuery();
			if (rs.next()) {
				// int empId = rs.getInt(1);
				int deptId = rs.getInt("dept_Id");
				int roled = rs.getInt("role_Id");
				String empName = rs.getString("emp_Name");
				String password = rs.getString("password");
				Double sal = rs.getDouble("sal");
				String idNumber = rs.getString("id_Number");
				String adderss = rs.getString("address");
				String phoneNumber = rs.getString("phone_Number");
				String sex = rs.getString("sex");
				String email = rs.getString("email");
				String education = rs.getString("education");
				Date hiredate = rs.getDate("hiredate");

				employee = new Employee(empId, deptId, roled, empName, password, sal, idNumber, adderss, phoneNumber,
						sex, email, education, hiredate);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return employee;
	}

	/**
	 * 查询所有记录
	 * 
	 * @return
	 */
	@Override
	public List<Employee> findAll() {
		List<Employee> list = new ArrayList<Employee>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			String sql = "select emp_Id,dept_Id,role_id,emp_Name,password,sal,id_Number,address, phone_Number,sex,email,education,hiredate from employee";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int empId = rs.getInt(1);
				int deptId = rs.getInt("dept_Id");
				int roleId = rs.getInt("role_id");
				String empName = rs.getString("emp_Name");
				String password = rs.getString("password");
				Double sal = rs.getDouble("sal");
				String idNumber = rs.getString("id_Number");
				String adderss = rs.getString("address");
				String phoneNumber = rs.getString("phone_Number");
				String sex = rs.getString("sex");
				String email = rs.getString("email");
				String education = rs.getString("education");
				Date hiredate = rs.getDate("hiredate");
				Employee employee = new Employee(empId, deptId, roleId, empName, password, sal, idNumber, adderss,
						phoneNumber, sex, email, education, hiredate);
				list.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	/**
	 * 模糊查询所有记录
	 * 
	 * @param param
	 * @return
	 */
	@Override
	public List<Employee> findByExample(Employee param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<Employee> list = new ArrayList<Employee>();
		ResultSet rs = null;
		try {
			String sql = "select empId,dept_Id,role_id,emp_Name,password,sal,\r\n"
					+ "						 id_Number,address,phone_Number,sex,email,education,hiredate where emp_Name like ?";

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "%" + param.getEmpName() + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				int empId = rs.getInt(1);
				int deptId = rs.getInt("deptId");
				int roleId = rs.getInt("roleId");
				String empName = rs.getString("empName");
				String password = rs.getString("password");
				Double sal = rs.getDouble("sal");
				String idNumber = rs.getString("idNumber");
				String adderss = rs.getString("adderss");
				String phoneNumber = rs.getString("phoneNumber");
				String sex = rs.getString("sex");
				String email = rs.getString("email");
				String education = rs.getString("education");
				Date hiredate = rs.getDate("hiredate");
				Employee employee = new Employee(empId, deptId, roleId, empName, password, sal, idNumber, adderss,
						phoneNumber, sex, email, education, hiredate);
				list.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	/**
	 * 分页查询
	 * 
	 * @param page 页码，从1开始，1表示第一页
	 * @param size 分页大小，最小值是1，表示每页查询1条记录
	 * @return
	 */
	@Override
	public List<Employee> findByPager(int page, int size) {
		List<Employee> list = new ArrayList<Employee>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from" + "(select rownum rn,empId,dept_Id,role_id,emp_Name,password,sal,\r\n"
					+ "id_Number,address,phone_Number,sex,email,education,hiredate from employee where rownum<=?)where rn> ? ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, page * size);
			stmt.setInt(2, (page - 1) * size);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int empId = rs.getInt("empId");
				int deptId = rs.getInt("deptId");
				int roleId = rs.getInt("roleId");
				String empName = rs.getString("empName");
				String password = rs.getString("password");
				Double sal = rs.getDouble("sal");
				String idNumber = rs.getString("idNumber");
				String adderss = rs.getString("adderss");
				String phoneNumber = rs.getString("phoneNumber");
				String sex = rs.getString("sex");
				String email = rs.getString("email");
				String education = rs.getString("education");
				Date hiredate = rs.getDate("hiredate");
				Employee employee = new Employee(empId, deptId, roleId, empName, password, sal, idNumber, adderss,
						phoneNumber, sex, email, education, hiredate);
				list.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	/**
	 * 根据指定的id数组，进行批量删除
	 * 
	 * @param ids
	 * @return
	 */
	@Override
	public int deleteByIds(int[] ids) {
		int result = 0;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "delete from employee where empId =?";
			stmt = conn.prepareStatement(sql);
			for (int empId : ids) {
				stmt.setInt(1, empId);
				stmt.addBatch();

			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == ids.length) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return result;
	}

	/**
	 * 批量新增
	 * 
	 * @param param
	 * @return
	 */
	@Override
	public int addAll(List<Employee> useparamrs) {
		int result = 0;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			conn.setAutoCommit(false);
			String sql = "insert into employee(emp_Id,dept_Id,role_id,emp_Name,password,sal,id_Number,address,phone_Number,sex,email,education,hiredate) values(SEQ_CUSTOMER.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			for (Employee i : useparamrs) {

				stmt.setInt(1, i.getEmpId());
				stmt.setInt(2, i.getRoleId());
				stmt.setString(3, i.getEmpName());
				stmt.setString(4, i.getPassword());
				stmt.setDouble(5, i.getSal());
				stmt.setString(6, i.getPhoneNumber());
				stmt.setString(7, i.getAdderss());
				stmt.setString(8, i.getPhoneNumber());
				stmt.setString(9, i.getSex());
				stmt.setString(10, i.getEmail());
				stmt.setString(11, i.getEducation());
				stmt.setDate(12, (java.sql.Date) i.getHiredate());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == useparamrs.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return result;
	}

	/**
	 * 批量修改
	 * 
	 * @param param
	 * @return
	 */
	@Override
	public int editAll(List<Employee> param) {

		return 0;
	}

}
